package com.scalar.forecast.projectSetup.dao;

import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.ScalarException;
import com.scalar.forecast.projectSetup.forms.UserDefinedFieldsForm;
import com.scalar.forecast.UserDefinedField;
import com.scalar.forecast.utils.ForecastSqlQueryMappings;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;


/**
 * Data Access Class for User Defined Fields for Project Setup
 *
 * Created by Veeru.
 * Date: Feb 9, 2005
 * Time: 8:31:54 PM
 */

public class UserDefinedFieldsDAO {

    /*
     *   Loads the user defined field names
     */

    public static void loadUserDefinedFieldNames( DBTransaction dbTransaction, UserDefinedFieldsForm userDefinedFieldsForm ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get( "forecast.projectSetup.userDefinedFields.loadFieldNames" ) );
            rs = pstmt.executeQuery();
            while ( rs.next() ) {
                userDefinedFieldsForm.setList1( rs.getString( "USERLIST1" ) );
                userDefinedFieldsForm.setList2( rs.getString( "USERLIST2" ) );
                userDefinedFieldsForm.setList3( rs.getString( "USERLIST3" ) );
                userDefinedFieldsForm.setDate1( rs.getString( "USERDATE1" ) );
                userDefinedFieldsForm.setDate2( rs.getString( "USERDATE2" ) );
                userDefinedFieldsForm.setText1( rs.getString( "USERTXT1" ) );
                userDefinedFieldsForm.setText2( rs.getString( "USERTXT2" ) );
                userDefinedFieldsForm.setText3( rs.getString( "USERTXT3" ) );
                userDefinedFieldsForm.setText4( rs.getString( "USERTXT4" ) );
                userDefinedFieldsForm.setText5( rs.getString( "USERTXT5" ) );

                userDefinedFieldsForm.setList4( rs.getString( "USERLIST4" ) );
                userDefinedFieldsForm.setList5( rs.getString( "USERLIST5" ) );
                userDefinedFieldsForm.setList6( rs.getString( "USERLIST6" ) );
                userDefinedFieldsForm.setDate3( rs.getString( "USERDATE3" ) );
                userDefinedFieldsForm.setDate4( rs.getString( "USERDATE4" ) );
                userDefinedFieldsForm.setText6( rs.getString( "USERTXT6" ) );
                userDefinedFieldsForm.setText7( rs.getString( "USERTXT7" ) );
                userDefinedFieldsForm.setText8( rs.getString( "USERTXT8" ) );
                userDefinedFieldsForm.setText9( rs.getString( "USERTXT9" ) );
                userDefinedFieldsForm.setText10( rs.getString( "USERTXT10" ) );
            }
            success = true;

        } catch( Exception e ) {
            e.printStackTrace();
            throw new ScalarException("Could not load User Defined Fields from database");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
    }

    /*
     *   Saves the user defined field names
     */
    public static void saveUserDefinedFieldNames( DBTransaction dbTransaction, String tableName, UserDefinedFieldsForm userDefinedFieldsForm) throws ScalarException {

        Connection        conn              = null;
        CallableStatement cstmt             = null;
        ResultSet         rs                = null;
        boolean           success           = false;

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }else {
                conn = dbTransaction.getConnection();
            }
            cstmt = conn.prepareCall( ForecastSqlQueryMappings.get( "forecast.projectSetup.userDefinedFields.saveFieldNames") );

            cstmt.setString( 1, tableName);
            cstmt.setString( 2, userDefinedFieldsForm.getList1() );
            cstmt.setString( 3, userDefinedFieldsForm.getList2() );
            cstmt.setString( 4, userDefinedFieldsForm.getList3() );
            cstmt.setString( 5, userDefinedFieldsForm.getDate1() );
            cstmt.setString( 6, userDefinedFieldsForm.getDate2() );
            cstmt.setString( 7, userDefinedFieldsForm.getText1() );
            cstmt.setString( 8, userDefinedFieldsForm.getText2() );
            cstmt.setString( 9, userDefinedFieldsForm.getText3() );
            cstmt.setString( 10, userDefinedFieldsForm.getText4() );
            cstmt.setString( 11, userDefinedFieldsForm.getText5() );

            cstmt.setString( 12, userDefinedFieldsForm.getList4() );
            cstmt.setString( 13, userDefinedFieldsForm.getList5() );
            cstmt.setString( 14, userDefinedFieldsForm.getList6() );
            cstmt.setString( 15, userDefinedFieldsForm.getDate3() );
            cstmt.setString( 16, userDefinedFieldsForm.getDate4() );
            cstmt.setString( 17, userDefinedFieldsForm.getText6() );
            cstmt.setString( 18, userDefinedFieldsForm.getText7() );
            cstmt.setString( 19, userDefinedFieldsForm.getText8() );
            cstmt.setString( 20, userDefinedFieldsForm.getText9() );
            cstmt.setString( 21, userDefinedFieldsForm.getText10() );

            cstmt.executeUpdate();
            success = true;


        } catch ( Exception e ) {
            e.printStackTrace();
            throw new ScalarException ( "Could not save Defined Field Names");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (cstmt != null)
             {
                 try { cstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
    }
    public static ArrayList loadUserDefinedListValuesForProjectSetup( DBTransaction dbTransaction, String tableName, String fieldName, int projectId  ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        ArrayList userDefinedFieldsList = new ArrayList();

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( "SELECT DISTINCT ENTRYVALUE FROM FCUENTRYVAL WHERE TABLENAME = ? AND  ENTRYNAME = ? " );
            pstmt.setString( 1, tableName );
            pstmt.setString( 2, fieldName );
            rs = pstmt.executeQuery();
            while ( rs.next() ) {
                userDefinedFieldsList.add( rs.getString("ENTRYVALUE") );
            }
            success = true;

        } catch( Exception e ) {
            e.printStackTrace();
            throw new ScalarException("Could not load User Defined Fields List from database");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return userDefinedFieldsList;
    }

    public static ArrayList loadUserDefinedListValues( DBTransaction dbTransaction, int projectId, String tableName, String listName ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        String listValue = null;
        ArrayList userDefinedFieldsList = new ArrayList();

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT DISTINCT ENTRYVALUE FROM FCUENTRYVAL WHERE TABLENAME = ? AND ENTRYNAME = ? ");
            pstmt.setString( 1, tableName );
            pstmt.setString( 2, listName );

            rs = pstmt.executeQuery();
            while ( rs.next() ) {
                listValue = rs.getString("ENTRYVALUE");
                userDefinedFieldsList.add( new UserDefinedField( listName, listValue ) );
            }
            if  ( userDefinedFieldsList.size() < 1 ) {
                userDefinedFieldsList.add( new UserDefinedField( listName, "" ) )  ;
            }
            success = true;

        } catch( Exception e ) {
            e.printStackTrace();
            throw new ScalarException("Could not load User Defined Fields List from database");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return userDefinedFieldsList;
    }

    public static ArrayList loadUserDefinedFieldValues( DBTransaction dbTransaction, int projectId, String tableName ) throws ScalarException {

        Connection        conn  = null;
        PreparedStatement pstmt = null;
        ResultSet         rs    = null;

        boolean success   = false;
        String  userText1 = null;
        String  userText2 = null;
        String  userText3 = null;
        String  userText4 = null;
        String  userText5 = null;
        String  userText6 = null;
        String  userText7 = null;
        String  userText8 = null;
        String  userText9 = null;
        String  userText10 = null;

        String  userDate1 = null;
        String  userDate2 = null;
        String  userDate3 = null;
        String  userDate4 = null;

        String  userList1 = null;
        String  userList2 = null;
        String  userList3 = null;
        String  userList4 = null;
        String  userList5 = null;
        String  userList6 = null;

        String  userListValue1 = null;
        String  userListValue2 = null;
        String  userListValue3 = null;
        String  userListValue4 = null;
        String  userListValue5 = null;
        String  userListValue6 = null;

        ArrayList userDefinedFieldsList = new ArrayList();

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
            if ( projectId < 0 ) {
                pstmt = conn.prepareStatement( "SELECT USERTXT1, USERTXT2, USERTXT3, USERTXT4, USERTXT5, USERDATE1, USERDATE2, USERLIST1, USERLIST2, USERLIST3, USERTXT6, USERTXT7 , USERTXT8, USERTXT9, USERTXT10, USERDATE3, USERDATE4, USERLIST4, USERLIST5, USERLIST6 FROM PROJSETUP WHERE PROJID = ( SELECT MAX(PROJID) FROM PROJSETUP ) " );
            }else {
                pstmt = conn.prepareStatement( "SELECT USERTXT1, USERTXT2, USERTXT3, USERTXT4, USERTXT5, USERDATE1, USERDATE2, USERLIST1, USERLIST1VAL, USERLIST2, USERLIST2VAL, USERLIST3, USERLIST3VAL, USERTXT6, USERTXT7, USERTXT8, USERTXT9, USERTXT10, USERDATE3, USERDATE4, USERLIST4, USERLIST4VAL, USERLIST5, USERLIST5VAL, USERLIST6, USERLIST6VAL FROM PROJSETUP WHERE PROJID = ? " );
                pstmt.setInt( 1, projectId );
            }
            rs = pstmt.executeQuery();

            while ( rs.next() ) {

                userText1 = rs.getString("USERTXT1");
                if ( userText1 != null && userText1.trim().length() > 0 ) {
                    userDefinedFieldsList.add( new UserDefinedField( "USERTXT1", userText1, getUserDefinedValue( null, tableName, userText1, projectId )+"", "text" ) );
                }
                userText2 = rs.getString("USERTXT2");
                if ( userText2 != null && userText2.trim().length() > 0 ) {
                    userDefinedFieldsList.add( new UserDefinedField( "USERTXT2", userText2, getUserDefinedValue( null, tableName, userText2, projectId )+"", "text" ) );
                }
                userText3 = rs.getString("USERTXT3");
                if ( userText3 != null && userText3.trim().length() > 0 ) {
                    userDefinedFieldsList.add( new UserDefinedField( "USERTXT3", userText3, getUserDefinedValue( null, tableName, userText3, projectId )+"", "text" ) );
                }
                userText4 = rs.getString("USERTXT4");
                if ( userText4 != null && userText4.trim().length() > 0 ) {
                    userDefinedFieldsList.add( new UserDefinedField( "USERTXT4", userText4, getUserDefinedValue( null, tableName, userText4, projectId )+"", "text" ) );
                }
                userText5 = rs.getString("USERTXT5");
                if ( userText5 != null && userText5.trim().length() > 0 ) {
                    userDefinedFieldsList.add( new UserDefinedField( "USERTXT5", userText5, getUserDefinedValue( null, tableName, userText5, projectId )+"", "text" ) );
                }

                userDate1 = rs.getString("USERDATE1");
                userDate2 = rs.getString("USERDATE2");
                userList1 = rs.getString("USERLIST1");
                userList2 = rs.getString("USERLIST2");
                userList3 = rs.getString("USERLIST3");

                userListValue1 = projectId < 0 ? "" : rs.getString("USERLIST1VAL");
                userListValue2 = projectId < 0 ? "" : rs.getString("USERLIST2VAL");
                userListValue3 = projectId < 0 ? "" : rs.getString("USERLIST3VAL");

                userText6 = rs.getString("USERTXT6");
                if ( userText6 != null && userText6.trim().length() > 0 ) {
                    userDefinedFieldsList.add( new UserDefinedField( "USERTXT6", userText6, getUserDefinedValue( null, tableName, userText6, projectId )+"", "text" ) );
                }
                userText7 = rs.getString("USERTXT7");
                if ( userText7 != null && userText7.trim().length() > 0 ) {
                    userDefinedFieldsList.add( new UserDefinedField( "USERTXT7", userText7, getUserDefinedValue( null, tableName, userText7, projectId )+"", "text" ) );
                }
                userText8 = rs.getString("USERTXT8");
                if ( userText8 != null && userText8.trim().length() > 0 ) {
                    userDefinedFieldsList.add( new UserDefinedField( "USERTXT8", userText8, getUserDefinedValue( null, tableName, userText8, projectId )+"", "text" ) );
                }
                userText9 = rs.getString("USERTXT9");
                if ( userText9 != null && userText9.trim().length() > 0 ) {
                    userDefinedFieldsList.add( new UserDefinedField( "USERTXT9", userText9, getUserDefinedValue( null, tableName, userText9, projectId )+"", "text" ) );
                }
                userText10 = rs.getString("USERTXT10");
                if ( userText10 != null && userText10.trim().length() > 0 ) {
                    userDefinedFieldsList.add( new UserDefinedField( "USERTXT10", userText10, getUserDefinedValue( null, tableName, userText10, projectId )+"", "text" ) );
                }
                if ( userDate1 != null && userDate1.trim().length() > 0 ) {
                    userDefinedFieldsList.add( new UserDefinedField( "USERDATE1", userDate1, getUserDefinedValue( null, tableName, userDate1, projectId )+"", "date" ) );
                }
                if ( userDate2 != null && userDate2.trim().length() > 0 ) {
                    userDefinedFieldsList.add( new UserDefinedField( "USERDATE2", userDate2, getUserDefinedValue( null, tableName, userDate2, projectId )+"", "date" ) );
                }
                userDate3 = rs.getString("USERDATE3");
                if ( userDate3 != null && userDate3.trim().length() > 0 ) {
                    userDefinedFieldsList.add( new UserDefinedField( "USERDATE3", userDate3, getUserDefinedValue( null, tableName, userDate3, projectId )+"", "date" ) );
                }

                userDate4 = rs.getString("USERDATE4");
                if ( userDate4 != null && userDate4.trim().length() > 0 ) {
                    userDefinedFieldsList.add( new UserDefinedField( "USERDATE4", userDate4, getUserDefinedValue( null, tableName, userDate4, projectId )+"", "date" ) );
                }
                userList4 = rs.getString("USERLIST4");
                userList5 = rs.getString("USERLIST5");
                userList6 = rs.getString("USERLIST6");

                userListValue4 = projectId < 0 ? "" : rs.getString("USERLIST4VAL");
                userListValue5 = projectId < 0 ? "" : rs.getString("USERLIST5VAL");
                userListValue6 = projectId < 0 ? "" : rs.getString("USERLIST6VAL");

            }

            if ( userList1 != null && userList1.trim().length() > 0 ) {
                userDefinedFieldsList.add( new UserDefinedField( "USERLIST1", userList1, userListValue1, "list", loadUserDefinedListValuesForProjectSetup( null, "PROJSETUP", userList1, projectId ) ) );
            }

            if ( userList2 != null && userList2.trim().length() > 0 ) {
                userDefinedFieldsList.add( new UserDefinedField( "USERLIST2", userList2, userListValue2, "list", loadUserDefinedListValuesForProjectSetup( null, "PROJSETUP", userList2, projectId ) ) );
            }

            if ( userList3 != null && userList3.trim().length() > 0 ) {
                userDefinedFieldsList.add( new UserDefinedField( "USERLIST3", userList3, userListValue3, "list", loadUserDefinedListValuesForProjectSetup( null, "PROJSETUP", userList3, projectId  ) ) );
            }

            if ( userList4 != null && userList4.trim().length() > 0 ) {
                userDefinedFieldsList.add( new UserDefinedField( "USERLIST4", userList4, userListValue4, "list", loadUserDefinedListValuesForProjectSetup( null, "PROJSETUP", userList4, projectId ) ) );
            }

            if ( userList5 != null && userList5.trim().length() > 0 ) {
                userDefinedFieldsList.add( new UserDefinedField( "USERLIST5", userList5, userListValue5, "list", loadUserDefinedListValuesForProjectSetup( null, "PROJSETUP", userList5, projectId ) ) );
            }

            if ( userList6 != null && userList6.trim().length() > 0 ) {
                userDefinedFieldsList.add( new UserDefinedField( "USERLIST6", userList6, userListValue6, "list", loadUserDefinedListValuesForProjectSetup( null, "PROJSETUP", userList6, projectId  ) ) );
            }

            success = true;

        } catch( Exception e ) {
            e.printStackTrace();
            throw new ScalarException("Could not load User Defined Fields List from database");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return userDefinedFieldsList;
    }

    public static void saveUserDefinedFieldValues( DBTransaction dbTransaction, String tableName, int projectId, ArrayList userDefinedFieldValues ) throws ScalarException {

        Connection        conn              = null;
        CallableStatement cstmt             = null;
        ResultSet         rs                = null;
        boolean           success           = false;

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }else {
                conn = dbTransaction.getConnection();
            }

            for (int i = 0; i < userDefinedFieldValues.size(); i++) {
                UserDefinedField userDefinedField = ( UserDefinedField ) userDefinedFieldValues.get(i);
                if ( "list".equalsIgnoreCase( userDefinedField.getType() ) ){
                    cstmt = conn.prepareCall( ForecastSqlQueryMappings.get("forecast.projectSetup.userDefinedFields.saveListSelectedValues") );
                    cstmt.setString( 1, tableName );
                    cstmt.setString( 2, userDefinedField.getName() );
                    cstmt.setString( 3, userDefinedField.getValue() );
                    cstmt.setInt   ( 4, projectId );

                } else {
                    cstmt = conn.prepareCall( ForecastSqlQueryMappings.get("forecast.projectSetup.userDefinedFields.saveFieldValues") );
                    cstmt.setString( 1, tableName );
                    cstmt.setString( 2, userDefinedField.getName() );
                    cstmt.setString( 3, userDefinedField.getValue() );
                    cstmt.setInt   ( 4, projectId );
                }
                cstmt.execute();
                cstmt.close();
                cstmt = null;
            }
            success = true;
        } catch ( Exception e ) {
            e.printStackTrace();
            throw new ScalarException ( "Could not save User Defined field values");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (cstmt != null)
             {
                 try { cstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
    }
    private static String  getUserDefinedValue( DBTransaction dbTransaction, String tableName, String name, int projectId ) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        String userDefinedValue = null;

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( "SELECT ENTRYVALUE FROM FCUENTRYVAL WHERE TABLENAME = ? AND ENTRYNAME = ? AND PROJID = ?" );
            pstmt.setString( 1, tableName );
            pstmt.setString( 2, name );
            pstmt.setInt( 3, projectId );
            rs = pstmt.executeQuery();
            while ( rs.next() ) {
                userDefinedValue = rs.getString("ENTRYVALUE");
            }
            success = true;

        } catch( Exception e ) {
            e.printStackTrace();
            throw new ScalarException("Could not load User Defined Fields from database");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return userDefinedValue == null ? "" : userDefinedValue;
    }

    public static void saveUserDefinedListValues( DBTransaction dbTransaction, String tableName, int projectId, String listName, String[] userDefinedListValues ) throws ScalarException {

        Connection        conn              = null;
        PreparedStatement pstmt             = null;

        ResultSet         rs                = null;
        boolean           success           = false;
        String listValue = null;

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get( "forecast.projectSetup.userDefinedFields.deleteFields") );
            pstmt.setString( 1, tableName );
            pstmt.setString( 2, listName );
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get("forecast.projectSetup.userDefinedFields.insertFields") );
            for ( int i = 0; i < userDefinedListValues.length; i++ ) {
                listValue = userDefinedListValues[i];
                if ( listValue != null && listValue.trim().length() > 0 ) {
                    pstmt.setString( 1, tableName );
                    pstmt.setString( 2, listName );
                    pstmt.setString( 3, listValue );
                    pstmt.setInt( 4, projectId );
                    pstmt.addBatch();
                }
            }
            pstmt.executeBatch();
            success = true;
        } catch ( Exception e ) {
            e.printStackTrace();
            throw new ScalarException ( "Could not save Products");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
    }
    /*
    *  Builds user defined fields list which contains user defined field names from data base and values from session
    */

    public static ArrayList loadNewUserDefinedFieldValues( DBTransaction dbTransaction, int projectId, ArrayList oldUserDefinedFieldsList ) throws ScalarException {

        Connection        conn  = null;
        PreparedStatement pstmt = null;
        ResultSet         rs    = null;

        boolean success   = false;
        String  userText1 = null;
        String  userText2 = null;
        String  userText3 = null;
        String  userText4 = null;
        String  userText5 = null;
        String  userDate1 = null;
        String  userDate2 = null;
        String  userList1 = null;
        String  userList2 = null;
        String  userList3 = null;

        String  userText6 = null;
        String  userText7 = null;
        String  userText8 = null;
        String  userText9 = null;
        String  userText10 = null;
        String  userDate3 = null;
        String  userDate4 = null;
        String  userList4 = null;
        String  userList5 = null;
        String  userList6 = null;

        ArrayList userDefinedFieldsList = new ArrayList();
        UserDefinedField userDefinedField = null;

        LinkedHashMap userDefinedFieldsMap = new LinkedHashMap();

        for (int i = 0; i < oldUserDefinedFieldsList.size(); i++) {
            userDefinedField = (UserDefinedField) oldUserDefinedFieldsList.get(i);
            userDefinedFieldsMap.put( userDefinedField.getFieldName(), userDefinedField );
        }

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
            if ( projectId < 0 ) {
                pstmt = conn.prepareStatement( "SELECT USERTXT1, USERTXT2 , USERTXT3, USERTXT4, USERTXT5, USERDATE1, USERDATE2, USERLIST1, USERLIST2, USERLIST3, USERTXT6, USERTXT7 , USERTXT8, USERTXT9, USERTXT10, USERDATE3, USERDATE4, USERLIST4, USERLIST5, USERLIST6 FROM PROJSETUP WHERE PROJID = ( SELECT MAX(PROJID) FROM PROJSETUP ) " );
            }else {
                pstmt = conn.prepareStatement( "SELECT USERTXT1, USERTXT2 , USERTXT3, USERTXT4, USERTXT5, USERDATE1, USERDATE2, USERLIST1, USERLIST1VAL, USERLIST2, USERLIST2VAL, USERLIST3, USERLIST3VAL, USERTXT6, USERTXT7 , USERTXT8, USERTXT9, USERTXT10, USERDATE3, USERDATE4, USERLIST4, USERLIST4VAL, USERLIST5, USERLIST5VAL, USERLIST6, USERLIST6VAL FROM PROJSETUP WHERE PROJID = ? " );
                pstmt.setInt( 1, projectId );
            }
            rs = pstmt.executeQuery();

            while ( rs.next() ) {
                userText1 = rs.getString("USERTXT1");
                if ( userText1 != null && userText1.trim().length() > 0 ) {
                    userDefinedField = ( UserDefinedField )userDefinedFieldsMap.get( "USERTXT1" ) ;
                    userDefinedFieldsList.add( new UserDefinedField( "USERTXT1", userText1, userDefinedField == null ? "" : userDefinedField.getValue(), "text" ) );
                }
                userText2 = rs.getString("USERTXT2");
                if ( userText2 != null && userText2.trim().length() > 0 ) {
                    userDefinedField = ( UserDefinedField )userDefinedFieldsMap.get( "USERTXT2" ) ;
                    userDefinedFieldsList.add( new UserDefinedField( "USERTXT2", userText2, userDefinedField == null ? "" : userDefinedField.getValue(), "text" ) );
                }
                userText3 = rs.getString("USERTXT3");
                if ( userText3 != null && userText3.trim().length() > 0 ) {
                    userDefinedField = ( UserDefinedField )userDefinedFieldsMap.get( "USERTXT3" );
                    userDefinedFieldsList.add( new UserDefinedField( "USERTXT3", userText3, userDefinedField == null ? "" : userDefinedField.getValue(), "text" ) );
                }
                userText4 = rs.getString("USERTXT4");
                if ( userText4 != null && userText4.trim().length() > 0 ) {
                    userDefinedField = ( UserDefinedField )userDefinedFieldsMap.get( "USERTXT4" );
                    userDefinedFieldsList.add( new UserDefinedField( "USERTXT4", userText4, userDefinedField == null ? "" : userDefinedField.getValue(), "text" ) );
                }
                userText5 = rs.getString("USERTXT5");
                if ( userText5 != null && userText5.trim().length() > 0 ) {
                    userDefinedField =  ( UserDefinedField )userDefinedFieldsMap.get( "USERTXT5" );
                    userDefinedFieldsList.add( new UserDefinedField(  "USERTXT5", userText5, userDefinedField == null ? "" : userDefinedField.getValue(), "text" ) );
                }
                userDate1 = rs.getString("USERDATE1");
                userDate2 = rs.getString("USERDATE2");
                userList1 = rs.getString("USERLIST1");
                userList2 = rs.getString("USERLIST2");
                userList3 = rs.getString("USERLIST3");

                userText6 = rs.getString("USERTXT6");
                if ( userText6 != null && userText6.trim().length() > 0 ) {
                    userDefinedField = ( UserDefinedField )userDefinedFieldsMap.get( "USERTXT6" ) ;
                    userDefinedFieldsList.add( new UserDefinedField( "USERTXT6", userText6, userDefinedField == null ? "" : userDefinedField.getValue(), "text" ) );
                }
                userText7 = rs.getString("USERTXT7");
                if ( userText7 != null && userText7.trim().length() > 0 ) {
                    userDefinedField = ( UserDefinedField )userDefinedFieldsMap.get( "USERTXT7" ) ;
                    userDefinedFieldsList.add( new UserDefinedField( "USERTXT7", userText7, userDefinedField == null ? "" : userDefinedField.getValue(), "text" ) );
                }
                userText8 = rs.getString("USERTXT8");
                if ( userText8 != null && userText8.trim().length() > 0 ) {
                    userDefinedField = ( UserDefinedField )userDefinedFieldsMap.get( "USERTXT8" );
                    userDefinedFieldsList.add( new UserDefinedField( "USERTXT8", userText8, userDefinedField == null ? "" : userDefinedField.getValue(), "text" ) );
                }
                userText9 = rs.getString("USERTXT9");
                if ( userText9 != null && userText9.trim().length() > 0 ) {
                    userDefinedField = ( UserDefinedField )userDefinedFieldsMap.get( "USERTXT9" );
                    userDefinedFieldsList.add( new UserDefinedField( "USERTXT9", userText9, userDefinedField == null ? "" : userDefinedField.getValue(), "text" ) );
                }
                userText10 = rs.getString("USERTXT10");
                if ( userText10 != null && userText10.trim().length() > 0 ) {
                    userDefinedField =  ( UserDefinedField )userDefinedFieldsMap.get( "USERTXT10" );
                    userDefinedFieldsList.add( new UserDefinedField(  "USERTXT10", userText10, userDefinedField == null ? "" : userDefinedField.getValue(), "text" ) );
                }
                if ( userDate1 != null && userDate1.trim().length() > 0 ) {
                    userDefinedField =  ( UserDefinedField )userDefinedFieldsMap.get( "USERDATE1" );
                    userDefinedFieldsList.add( new UserDefinedField( "USERDATE1", userDate1, userDefinedField == null ? "" : userDefinedField.getValue(), "date" ) );
                }
                if ( userDate2 != null && userDate2.trim().length() > 0 ) {
                    userDefinedField =  ( UserDefinedField )userDefinedFieldsMap.get( "USERDATE2" );
                    userDefinedFieldsList.add( new UserDefinedField(  "USERDATE2", userDate2, userDefinedField == null ? "" : userDefinedField.getValue(), "date" ) );
                }
                userDate3 = rs.getString("USERDATE3");
                if ( userDate3 != null && userDate3.trim().length() > 0 ) {
                    userDefinedField =  ( UserDefinedField )userDefinedFieldsMap.get( "USERDATE3" );
                    userDefinedFieldsList.add( new UserDefinedField( "USERDATE3", userDate3, userDefinedField == null ? "" : userDefinedField.getValue(), "date" ) );
                }
                userDate4 = rs.getString("USERDATE4");
                if ( userDate4 != null && userDate4.trim().length() > 0 ) {
                    userDefinedField =  ( UserDefinedField )userDefinedFieldsMap.get( "USERDATE4" );
                    userDefinedFieldsList.add( new UserDefinedField(  "USERDATE4", userDate4, userDefinedField == null ? "" : userDefinedField.getValue(), "date" ) );
                }
                userList4 = rs.getString("USERLIST4");
                userList5 = rs.getString("USERLIST5");
                userList6 = rs.getString("USERLIST6");
            }
            if ( userList1 != null && userList1.trim().length() > 0 ) {
                userDefinedField =  ( UserDefinedField )userDefinedFieldsMap.get( "USERLIST1" ) ;
                userDefinedFieldsList.add( new UserDefinedField( "USERLIST1", userList1, userDefinedField == null ? "" : userDefinedField.getValue(), "list", loadUserDefinedListValuesForProjectSetup( null, "PROJSETUP", userList1, projectId ) ) );
            }
            if ( userList2 != null && userList2.trim().length() > 0 ) {
                 userDefinedField = ( UserDefinedField )userDefinedFieldsMap.get( "USERLIST2" ) ;
                userDefinedFieldsList.add( new UserDefinedField( "USERLIST2", userList2, userDefinedField == null ? "" : userDefinedField.getValue(), "list", loadUserDefinedListValuesForProjectSetup( null, "PROJSETUP", userList2, projectId ) ) );
            }
            if ( userList3 != null && userList3.trim().length() > 0 ) {
                 userDefinedField = ( UserDefinedField )userDefinedFieldsMap.get( "USERLIST3" );
                userDefinedFieldsList.add( new UserDefinedField( "USERLIST3", userList3, userDefinedField == null ? "" : userDefinedField.getValue(), "list", loadUserDefinedListValuesForProjectSetup( null, "PROJSETUP", userList3, projectId  ) ) );
            }

            if ( userList4 != null && userList4.trim().length() > 0 ) {
                userDefinedField =  ( UserDefinedField )userDefinedFieldsMap.get( "USERLIST4" ) ;
                userDefinedFieldsList.add( new UserDefinedField( "USERLIST4", userList4, userDefinedField == null ? "" : userDefinedField.getValue(), "list", loadUserDefinedListValuesForProjectSetup( null, "PROJSETUP", userList4, projectId ) ) );
            }
            if ( userList5 != null && userList5.trim().length() > 0 ) {
                 userDefinedField = ( UserDefinedField )userDefinedFieldsMap.get( "USERLIST5" ) ;
                userDefinedFieldsList.add( new UserDefinedField( "USERLIST5", userList5, userDefinedField == null ? "" : userDefinedField.getValue(), "list", loadUserDefinedListValuesForProjectSetup( null, "PROJSETUP", userList5, projectId ) ) );
            }
            if ( userList6 != null && userList6.trim().length() > 0 ) {
                 userDefinedField = ( UserDefinedField )userDefinedFieldsMap.get( "USERLIST6" );
                userDefinedFieldsList.add( new UserDefinedField( "USERLIST6", userList6, userDefinedField == null ? "" : userDefinedField.getValue(), "list", loadUserDefinedListValuesForProjectSetup( null, "PROJSETUP", userList6, projectId  ) ) );
            }

            success = true;

        } catch( Exception e ) {
            e.printStackTrace();
            throw new ScalarException("Could not load User Defined Fields List from database");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return userDefinedFieldsList;
    }
}
